#!/usr/bin/env Jython
# -*- coding: utf-8 -*-

import csv
import sys
import os
from com.ziclix.python.sql import zxJDBC

DATABASE    = "dbByPython.db"
JDBC_URL    = "jdbc:sqlite:%s"  % DATABASE
JDBC_DRIVER = "org.sqlite.JDBC"



def getEstablishment():
	
	
	dbConn = getConnection(JDBC_URL, JDBC_DRIVER)
    
	cursor = dbConn.cursor()
	
	cursor.execute("select distinct(Nome_do_Estabelecimento) from rebides")
	
	establishment = []
	
	for row in cursor:
		establishment.append(row[0])
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return establishment

def getYears():
	
	
	dbConn = getConnection(JDBC_URL, JDBC_DRIVER)
    
	cursor = dbConn.cursor()
	
	cursor.execute("select distinct(Ano_Lectivo) from rebides")
	
	years = []
	
	for row in cursor:
		years.append(row[0])
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return years

def getCategories():
	
	
	dbConn = getConnection(JDBC_URL, JDBC_DRIVER)
    
	cursor = dbConn.cursor()
	
	cursor.execute("select distinct(Categoria) from rebides")
	
	categories = []
	
	for row in cursor:
		categories.append(row[0])
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return categories
	
def getDegrees():
	
	
	dbConn = getConnection(JDBC_URL, JDBC_DRIVER)
    
	cursor = dbConn.cursor()
	
	cursor.execute("select distinct(ultimo_Grau) from rebides")
	
	degrees = []
	
	for row in cursor:
		degrees.append(row[0])
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return degrees

def getQuery(combo_box_1, combo_box_2, combo_box_3, combo_box_4):
	
	
	query = ""
	if(combo_box_1.isEnabled()==True):
		query = "select distinct(nome) from rebides where Nome_do_Estabelecimento = '" + combo_box_1.getSelectedItem() + "'"
		if(combo_box_2.isEnabled()==True):
			query += " and Categoria = '" + combo_box_2.getSelectedItem() + "'"
		if(combo_box_3.isEnabled()==True):
			query += " and ultimo_Grau = '" + combo_box_3.getSelectedItem() + "'"
		if(combo_box_4.isEnabled()==True): 
			query += " and Ano_Lectivo = '" + str(combo_box_4.getSelectedItem()) + "'"
	elif(combo_box_2.isEnabled()==True):
		query = "select distinct(nome) from rebides where Categoria = '" + combo_box_2.getSelectedItem() + "'"
		if(combo_box_3.isEnabled()==True):
			query += " and ultimo_Grau = '" + combo_box_3.getSelectedItem() + "'"
		if(combo_box_4.isEnabled()==True): 
			query += " and Ano_Lectivo = '" + str(combo_box_4.getSelectedItem()) + "'"
	elif(combo_box_3.isEnabled()==True):
		query = "select distinct(nome) from rebides where ultimo_Grau = '" + combo_box_3.getSelectedItem() + "'"
		if(combo_box_4.isEnabled()==True): 
			query += " and Ano_Lectivo = '" + str(combo_box_4.getSelectedItem()) + "'"
	elif(combo_box_4.isEnabled()==True):
		query = "select distinct(nome) from rebides where Ano_Lectivo = '" + combo_box_4.getSelectedItem() + "'"
	
	dbConn = getConnection(JDBC_URL, JDBC_DRIVER)
    
	cursor = dbConn.cursor()
	
	cursor.execute(query)
	
	result = []
	
	for row in cursor:
		result.append(row[0])	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return result

def getConnection(jdbc_url, driverName):
  
    try:
        dbConn = zxJDBC.connect(jdbc_url, None, None, driverName)
    except zxJDBC.DatabaseError, msg:
        print msg
        sys.exit(-1)

    return dbConn
